{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 实例20：按条件汇总Excel表中的数据-根据工单及物料编号加总不同批次的领料数量"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "生产部门领料，是按批次发料的。比如A物料需要领1000个，但货仓的库存有3个批次“01,02,03”，其对应数量是“100个，300个，5000个”。货仓发料的时候，会先把01批次的100个发完，然后再将02批次的300个发完，剩下的600个需求则从03批次中抽取。这样一个物料需求就生成了三条领料记录。类似如下这样的（这个是分两个批次发的）：\n",
    "![](images/example.png)\n",
    "现在老板要求将数量合并汇总给他，相当于将上面的两行合并成1行，将两个批次的数量加总：\n",
    "![](images/example1.png)\n",
    "此时，我们想到Excel的数据透视表功能，so easy！于是分分钟透视出来：\n",
    "![](images/pivot.png)\n",
    "但是，麻烦来了，怎么搞成老板要求的Excel表呢？他的要求可是这样的：\n",
    "![](images/boss.png)\n",
    "总不至于一个一个去透视表中复制吧，700多个工单，得复制到猴年马月啊？还是让Python来干吧。\n",
    "先理清思路\n",
    "1. 将原表数据提取出来，并将相同工单号和物料编号对应的批数量加起来\n",
    "2. 将提取的数据写到新的Excel表"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "#1.提取信息\n",
    "from openpyxl import load_workbook\n",
    "wb = load_workbook(\"data\\领料总表.xlsx\")\n",
    "ws= wb.active\n",
    "data = {} #用于储存提取的信息\n",
    "for row in range(2, ws.max_row+1): #从第2行开始（第1行是标题）遍历工作表每一行，将数据提取出来\n",
    "    work_order= ws['A' + str(row)].value #为工单号\n",
    "    unit = ws['B' + str(row)].value #为物料单位\n",
    "    material_pn = ws['C' + str(row)].value #为物料编号    \n",
    "    qty = ws['F' + str(row)].value #批数量\n",
    "\n",
    "    data.setdefault(work_order,{}) \n",
    "    data[work_order].setdefault(material_pn,{'单位':unit,\n",
    "                                            '总数':0}) #先设定“总数”的初始值为0\n",
    "    data[work_order][material_pn]['总数']+=int(qty) #让数量累加，确保所有批次的数量加总"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "还是用openpyxl模块读取工作簿，再读取工作表。此处只用到4个信息，即工单号，物料单位，物料编号及数量，分别提取出来，存入work_order,unit,material_pn,qty，然后按嵌套字典的方式存入data字典。此处用到setdefault函数，字面意思就是为字典设定默认键值。以字典WO20A510520 {'10010-0032400': {'单位': 'EA', '总数': 50}}为例，它最外层的键是WO20239156,这时会检查它是否已在字典的外层键中存在，若不存在，则设置为外层键；若已存在，则进入下一层，检查其包含的物料编号10010-0032400是否存在第二层键中,若不存在，则设置为第二层的键；若存在，则进入内层，并将“单位”和“总数”设置为内层键。“单位”的值为“unit”，“总数”的初始值设为0，然后让其累加。即当同一工单号中相同物料重复出现的时候，其数量累加。如此循环直到整个工作表3万多行数据全部读取完毕，并存入data。\n",
    "\n",
    "下面随机检查一下数据，看看有无问题。len(data)可查看字典data外层键的数量，其实就是对应的工单数，共733个，非常正确。然后再看看键值对及指定键的数据，都显示正常。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "733"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(data)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'10010-0032400': {'单位': 'EA', '总数': 50},\n",
       " '10105-0007900': {'单位': 'EA', '总数': 20},\n",
       " '10105-0008000': {'单位': 'EA', '总数': 20},\n",
       " '12080-0001900': {'单位': 'EA', '总数': 20},\n",
       " '36010-0239300': {'单位': 'EA', '总数': 20},\n",
       " '40011-0008300': {'单位': 'EA', '总数': 40},\n",
       " '40051-0001900': {'单位': 'EA', '总数': 20},\n",
       " '41001-0001770': {'单位': 'EA', '总数': 20},\n",
       " '41002-0001720': {'单位': 'EA', '总数': 20},\n",
       " '41003-0001210': {'单位': 'EA', '总数': 20},\n",
       " '41008-0000100': {'单位': 'EA', '总数': 40},\n",
       " '46000-0420050': {'单位': 'EA', '总数': 40},\n",
       " '46010-0425050': {'单位': 'EA', '总数': 20},\n",
       " '47005-0000130': {'单位': 'EA', '总数': 40},\n",
       " '47005-0000140': {'单位': 'EA', '总数': 20},\n",
       " '48022-0000190': {'单位': 'EA', '总数': 20},\n",
       " '59005-0000610': {'单位': 'EA', '总数': 20},\n",
       " '59006-0000270': {'单位': 'EA', '总数': 20},\n",
       " '60021-1004002': {'单位': 'EA', '总数': 20},\n",
       " '61011-1022000': {'单位': 'EA', '总数': 70},\n",
       " '61013-0001000': {'单位': 'EA', '总数': 70},\n",
       " '62020-0164762': {'单位': 'EA', '总数': 70},\n",
       " '63011-0000100': {'单位': 'EA', '总数': 25},\n",
       " '65021-0000070': {'单位': 'EA', '总数': 70},\n",
       " '68011-0000021': {'单位': 'EA', '总数': 30},\n",
       " '71101-0036400': {'单位': 'EA', '总数': 25}}"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data['WO20A510520']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "数据获取完成后，就可以开始写入汇总表了。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Wall time: 6.3 s\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "#2.将提取的信息写入新建的Excel表\n",
    "wb1 = load_workbook(\"data\\领料汇总-模板.xlsx\")\n",
    "ws1= wb1.active\n",
    "\n",
    "i=2 #计数器，从2开始（因为是从“领料汇总-模板”表中的第二行开始写）\n",
    "for work_order in data.keys():\n",
    "    for material in data[work_order].keys():\n",
    "        ws1.cell(row=i,column=1).value=work_order #第1列为工单号\n",
    "        ws1.cell(row=i,column=2).value=data[work_order][material][\"单位\"] #第2列为单位\n",
    "        ws1.cell(row=i,column=3).value=material #第3列为物料编号\n",
    "        ws1.cell(row=i,column=4).value=data[work_order][material][\"总数\"] #第4列为总数\n",
    "        i+=1\n",
    "wb1.save(\"data\\领料汇总.xlsx\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "三万多行数据的查询和写入，用时6秒，号称秒秒钟完成。此处，先打开需要写入数据的表“领料汇总-模板”，从第2行开始写入数据。按工单、物料编号两层逐层遍历数据字典data，将其中的数据逐个写入表格。数据写完后，保存工作表。最后检查一下作业，没问题，小功告成（对Python来说实在不算“大功”啊）。\n",
    "![](images\\result.png)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
